<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>MySQL数据库操作（五）——多表查询 | 海晨忆的博客</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  
  
  <meta name="description" content="合并结果集 union all(包含相同行)、union(去除相同行)&amp;emsp;&amp;emsp;把查询的结果合并到一起。要求： 查询的结果列数相同，列类型相同">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL数据库操作（五）——多表查询">
<meta property="og:url" content="https://haichenyi.com/2018/10/11/MySQL数据库操作（五）——多表查询/index.html">
<meta property="og:site_name" content="海晨忆的博客">
<meta property="og:description" content="合并结果集 union all(包含相同行)、union(去除相同行)&amp;emsp;&amp;emsp;把查询的结果合并到一起。要求： 查询的结果列数相同，列类型相同">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2018-10-11T15:03:11.105Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL数据库操作（五）——多表查询">
<meta name="twitter:description" content="合并结果集 union all(包含相同行)、union(去除相同行)&amp;emsp;&amp;emsp;把查询的结果合并到一起。要求： 查询的结果列数相同，列类型相同">
  
    <link rel="alternate" href="/atom.xml" title="海晨忆的博客" type="application/atom+xml">
  
  
    <link rel="icon" href="/uploads/artistic_image/head.jpg">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
  <link rel="stylesheet" href="/css/highlight.css">
  <script>
  let antiquityStorage = window.sessionStorage.getItem('antiquitySessionStorage');
  if (antiquityStorage == '' || antiquityStorage == null) {
    var antiquityLoader = '<div id="loaderbox"><div class="loader"><div class="load-roll"><div class="load-top"></div><div class="load-right"></div><div class="load-bottom"></div></div></div></div>';
    document.write(antiquityLoader);
    document.body.style.overflow = 'hidden'
  }
  </script>
</head>

<body>
  <div id="fullpage" class="mobile-nav-right">
    
      <div id="wrapper" style="background-image: url(/uploads/artistic_image/bg.jpg)" title="背景图片来自网络">
    
    
      <header id="header">
  <div id="nav-toggle" class="nav-toggle"></div>
  <div class="head-box global-width">
    <nav class="nav-box nav-right">
      
        <a class="nav-item" href="/" title
        
        >首页</a>
      
        <a class="nav-item" href="/archives" title
        
        >归档</a>
      
        <a class="nav-item" href="/about" title
        
        >简历</a>
      
    </nav>
  </div>
</header>
      <div id="middlecontent" title class="global-width sidebar-left">
        <section id="main"><article id="post-MySQL数据库操作（五）——多表查询" class="article global-container article-type-post" itemscope itemprop="blogPost">
  
    <header class="article-header">
      
  
    <h1 class="article-title" itemprop="name">
      MySQL数据库操作（五）——多表查询
    </h1>
  

    </header>
  
  <div class="article-meta">
    <a href="/2018/10/11/MySQL数据库操作（五）——多表查询/" class="article-date">
  <time datetime="2018-10-11T14:50:45.000Z" itemprop="datePublished">2018-10-11</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/数据库-MySQL/">数据库 -MySQL</a>
  </div>

    
  </div>
  
    <span id="busuanzi_container_page_pv">
      本文总阅读量<span id="busuanzi_value_page_pv"></span>次
    </span>
  

  <div class="article-inner">
    
    <div class="article-content article-content-cloud doorframe mac" itemprop="articleBody">
      
        <h3 id="合并结果集-union-all-包含相同行-、union-去除相同行"><a href="#合并结果集-union-all-包含相同行-、union-去除相同行" class="headerlink" title="合并结果集 union all(包含相同行)、union(去除相同行)"></a>合并结果集 <code>union all(包含相同行)、union(去除相同行)</code></h3><p>&emsp;&emsp;把查询的结果合并到一起。<strong>要求：</strong> <code>查询的结果列数相同，列类型相同</code></p>
<a id="more"></a>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">create table emp(</span><br><span class="line">    eid int,</span><br><span class="line">    ename varchar(50),</span><br><span class="line">    eage int</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">create table stu(</span><br><span class="line">    sid int,</span><br><span class="line">    sname varchar(50)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">//合并结果集（加上all，包含相同的行）</span><br><span class="line">select eid,ename from emp</span><br><span class="line">union all</span><br><span class="line">select * from stu;</span><br><span class="line"></span><br><span class="line">//合并结果集（去掉all，去除相同行）</span><br><span class="line">select eid,ename from emp</span><br><span class="line">union</span><br><span class="line">select * from stu;</span><br></pre></td></tr></table></figure></div>
<h3 id="连接查询"><a href="#连接查询" class="headerlink" title="连接查询"></a>连接查询</h3><h4 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h4><p><strong>方言：</strong> <code>select * from 表1,表2 where 条件</code></p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">表a有三条数据：q，w，e</span><br><span class="line">表b有两条数据：h，j</span><br><span class="line">select * from a,b;</span><br><span class="line">得出的结果是表a与表b的笛卡尔集，也就是会得到6条数据，</span><br><span class="line">分别：qh,qj,wh,wj,eh,ej</span><br></pre></td></tr></table></figure></div>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">去除无用的笛卡尔集，加上where后面的条件</span><br><span class="line">连表查询会有附表的外键=主表的主键这个条件</span><br><span class="line"></span><br><span class="line">select * from student,teacher where student.tid=teacher.tid;</span><br><span class="line"></span><br><span class="line">查询学生的全部信息，需要查询学生表和老师表，</span><br><span class="line">条件是学生表里面的老师id等于老师表里面的老师id，</span><br><span class="line">这样查出来的结果就是我们想要的结果</span><br></pre></td></tr></table></figure></div>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">例子：要求打印员工姓名，工资以及部分名称</span><br><span class="line">     员工姓名，工资在emp表，部门名称在dept表</span><br><span class="line">select emp.ename,emp.sal,dept.dname</span><br><span class="line">from emp,dept</span><br><span class="line">where emp.did=dept.did;</span><br><span class="line"></span><br><span class="line">如果嫌弃表名字太长了可以这样：</span><br><span class="line">select e.ename,e.sal,d.dname</span><br><span class="line">from emp e,dept d</span><br><span class="line">where e.did=d.did;</span><br></pre></td></tr></table></figure></div>
<p><strong>标准：</strong> <code>select * from 表1 别名1 inner join 表2 别名2 on 条件</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">别名都是可选的，条件跟上面写的一样。两者的区别就只有二个：</span><br><span class="line">1、方言连接两个表是用逗号，标准连接两个表是用 inner join</span><br><span class="line">2、条件：方言用where，标准用on</span><br><span class="line"></span><br><span class="line">select e.ename,e.sal,d.dname</span><br><span class="line">from emp e inner join dept d</span><br><span class="line">where e.did=d.did;</span><br></pre></td></tr></table></figure></div></p>
<h4 id="外连接-一主一次"><a href="#外连接-一主一次" class="headerlink" title="外连接 一主一次"></a>外连接 <code>一主一次</code></h4><p><strong>左外连接</strong> <code>left outer join ,左表为主表，右表为次表,左表中不论满足不满足条件都查询出来，右表中对应的位置用null补位</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">查询所有员工名称，工资以及部门名称，部门名称为null的填无部分</span><br><span class="line">select e.ename as 姓名,e.sal as 工资,d.ifnull(dname,&apos;无部门&apos;) as 部门名称</span><br><span class="line">from emp e left outer join dept d</span><br><span class="line">where e.did=d.did;</span><br></pre></td></tr></table></figure></div></p>
<p><strong>右外连接</strong> <code>right outer join 与左外对应</code></p>
<h3 id="子查询-一条sql语句中有多个select关键字-ALL-ANY-IN"><a href="#子查询-一条sql语句中有多个select关键字-ALL-ANY-IN" class="headerlink" title="子查询 一条sql语句中有多个select关键字,ALL,ANY,IN"></a>子查询 <code>一条sql语句中有多个select关键字,ALL,ANY,IN</code></h3><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">//查询员工中最高工资员工的详细信息</span><br><span class="line">select * from emp where sal=MAX(sal);</span><br><span class="line">//上面这个写法是错误的，条件中是不能出现集合函数</span><br><span class="line">//上面这个写法是错误的，条件中是不能出现集合函数</span><br><span class="line">//上面这个写法是错误的，条件中是不能出现集合函数</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">//1、先查最高工资</span><br><span class="line">select MAX(sal) from emp;</span><br><span class="line">//2、最高工资的员工信息</span><br><span class="line">select * from emp where sal=(select MAX(sal) from emp);</span><br><span class="line">//上面这个写法才是正确的</span><br><span class="line">//上面这个写法才是正确的</span><br><span class="line">//上面这个写法才是正确的</span><br></pre></td></tr></table></figure></div>
<p><strong>出现的位置：</strong> <code>1、from后作为表 2、where后作为条件</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">上面那条sql语句就是作为条件</span><br><span class="line">//查询部门等于30的员工的姓名和年龄</span><br><span class="line">select e.name,e.age from (select * from emp where did=30) e;</span><br><span class="line">//上面这条就是from后面作为表</span><br></pre></td></tr></table></figure></div></p>
<p><strong>1、打印高于平均工资的所有人的信息(单行单列作为条件)</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from emp where (sal&gt;select AVG(sal) from emp);</span><br></pre></td></tr></table></figure></div></p>
<p><strong>2、打印大于30部门的所有员工工资的员工信息(多行单列作为条件)</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from emp where sal &gt; ALL (select sal from emp where did=30);</span><br></pre></td></tr></table></figure></div></p>
<p><strong>3、打印大于30部门任意一人员工工资的员工信息</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from emp where sal&gt; ANY (select sal from emp where did = 30);</span><br></pre></td></tr></table></figure></div></p>
<p><strong>4、打印工作和部门与张三相同的员工信息</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from emp where (job,did) IN (select job,did from emp where ename=&apos;张三&apos;);</span><br></pre></td></tr></table></figure></div></p>
<h3 id="练习题"><a href="#练习题" class="headerlink" title="练习题"></a>练习题</h3><p><strong><em>前提条件已知四张表</em></strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">员工表emp(eid,ename,job,emid,etime,sal,edid)</span><br><span class="line">部门表dept(did,dname,dloc)</span><br><span class="line">薪资等级表salgrade(grade,losal,hisal)</span><br><span class="line">领导表mgr(mid,mname,mtime)</span><br></pre></td></tr></table></figure></div></p>
<p><strong>1、查询至少有一个员工的部门，显示部门编号，部门名称，部门位置，部门人数</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：d.did，d.dname，d.dloc，部门人数</span><br><span class="line">2、表：dept d，emp e</span><br><span class="line">条件：e.did=d.did</span><br><span class="line"></span><br><span class="line">//查询部门编号，部门名称，部门位置</span><br><span class="line">select * from dept;</span><br><span class="line"></span><br><span class="line">//查询部门人数</span><br><span class="line">select e.did,count(*) from emp group by e.did;</span><br><span class="line"></span><br><span class="line">//把上面查询的两张表内连接</span><br><span class="line">select * from dept inner join select e.did,count(*) from emp group by e.did;</span><br><span class="line"></span><br><span class="line">//加上条件，给出别名</span><br><span class="line">select d.*,z1.cnt from dept d inner join (select did,count(*) cnt from emp group by e.did) z1 where d.did=z1.did;</span><br></pre></td></tr></table></figure></div></p>
<p><strong>2、查询所有员工名称及其直接上级领导名称</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：e.ename，m.mname</span><br><span class="line">2、表：emp e，mgr m</span><br><span class="line">条件：e.emid=m.mid</span><br><span class="line">//查询员工名称</span><br><span class="line">select ename from emp;</span><br><span class="line">//查询领导名称</span><br><span class="line">select mname from mgr;</span><br><span class="line"></span><br><span class="line">select ename from emp e left outer join (select mname from mgr ) m on e.emid = m.mid;</span><br></pre></td></tr></table></figure></div></p>
<p><strong>3、列出受雇佣日期早于直接上级的所有员工编号，姓名，以及部门名称</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：e.eid,e.ename,d.dname</span><br><span class="line">2、表：emp，mgr，dept</span><br><span class="line">//查询受雇佣日期早于直接上级领导的员工信息</span><br><span class="line">select etime from emp;</span><br><span class="line">select mtime from mgr;</span><br><span class="line"></span><br><span class="line">select * from emp e</span><br><span class="line">inner join </span><br><span class="line">select mtime from mgr m</span><br><span class="line">on e.emid=m.mid and e.etime&lt;m.mtime</span><br><span class="line"></span><br><span class="line">//再把部门名称加上</span><br><span class="line">(select eid,ename,edid from emp e</span><br><span class="line">inner join </span><br><span class="line">select mtime from mgr m</span><br><span class="line">on e.emid=m.mid and e.etime&lt;m.mtime) z1 </span><br><span class="line">left outer join </span><br><span class="line">select dname from dept d </span><br><span class="line">on z1.edid = d.did;</span><br><span class="line"></span><br><span class="line">或者</span><br><span class="line">select e.eid,e.ename,d.dname</span><br><span class="line">from emp e,mgr m,dept d</span><br><span class="line">where e.emid=m.mid and e.etime&lt;m.mtime and e.edid=d.did;</span><br></pre></td></tr></table></figure></div></p>
<p><strong>4、列出部门名称和这些部门的员工信息，同事列出没有员工的部门</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：员工信息，和部门名称，没有员工的部门也要列出来</span><br><span class="line">2、表：emp,dept</span><br><span class="line"></span><br><span class="line">select e.*,d.dname from emp e </span><br><span class="line">right outer join </span><br><span class="line">dept d</span><br><span class="line">on e.edid=d.did;</span><br></pre></td></tr></table></figure></div></p>
<p><strong>5、列出最低薪资大于15000的各种工作以及从事此工作的人数</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：工作类型，人数</span><br><span class="line">2、表：emp</span><br><span class="line">条件：min(sal)&gt;15000</span><br><span class="line"></span><br><span class="line">select job,count(*)</span><br><span class="line">from emp</span><br><span class="line">group by job</span><br><span class="line">having min(sal)&gt;15000</span><br></pre></td></tr></table></figure></div></p>
<p><strong>6、列出在销售部工作的员工姓名，假定不知道销售部的部门编号</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：员工姓名</span><br><span class="line">2、表：emp,dept</span><br><span class="line">条件：e.edid=d.did</span><br><span class="line"></span><br><span class="line">select ename</span><br><span class="line">from emp e</span><br><span class="line">where e.edid=(select did from dept where dname=&apos;销售部&apos;);</span><br></pre></td></tr></table></figure></div></p>
<p><strong>7、列出薪资高于公司平均工资的所有员工信息，所在部门名称，上级领导，工资等级</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：员工信息，部门名称，上级领导名称，工资等级</span><br><span class="line">2、表：emp,dept,mgr</span><br><span class="line">条件：sal&gt;avg(sal),e.edid=d.did,e.emid=m.mid</span><br><span class="line">//先查询高出平均工资的员工信息</span><br><span class="line">select * from emp where sal&gt;(select avg(sal) from emp);</span><br><span class="line"></span><br><span class="line">//把后面的表一个一个的加上去</span><br><span class="line">//加部门名称</span><br><span class="line">select e.*,d.dname</span><br><span class="line">from </span><br><span class="line">    emp e left outer join dept d on e.edid=d.did</span><br><span class="line">where e.sal&gt;(select avg(sal) from emp)</span><br><span class="line"></span><br><span class="line">//加上级领导名称</span><br><span class="line">select e.*,d.dname,m.mname</span><br><span class="line">from </span><br><span class="line">    emp e left outer join dept d on e.edid=d.did</span><br><span class="line">    left outer join mgr m on e.emid = m.mid</span><br><span class="line">where e.sal&gt;(select avg(sal) from emp)</span><br><span class="line"></span><br><span class="line">//最后加工资等级</span><br><span class="line">select e.*,d.dname,m.mname,s.grade</span><br><span class="line">from </span><br><span class="line">    emp e left outer join dept d on e.edid=d.did</span><br><span class="line">    left outer join mgr m on e.emid = m.mid</span><br><span class="line">    left outer join salgrade s on e.sal between s.losal and hisal</span><br><span class="line">where e.sal&gt;(select avg(sal) from emp)</span><br></pre></td></tr></table></figure></div></p>
<p><strong>8、列出与张三从事相同工作的所有员工以及部门名称</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">分析：</span><br><span class="line">1、列：所有员工，部门名称</span><br><span class="line">2、表：emp，dept</span><br><span class="line">条件：张三的工作相同，e.edid=d.did</span><br><span class="line"></span><br><span class="line">select e.*,d.dname from emp e </span><br><span class="line">left outer join dept d</span><br><span class="line">on e.edid=d.did</span><br><span class="line">where e.job=(select job from emp where ename=&apos;张三&apos;);</span><br><span class="line"></span><br><span class="line">或者</span><br><span class="line"></span><br><span class="line">select e.*,d.dname from emp e,dept d where e.edid=did and e.job=(select job from emp where ename=&apos;张三&apos;);</span><br></pre></td></tr></table></figure></div></p>
<p><strong>9、列出薪资高于30部门的所有员工薪资的员工的姓名和薪资，部门名称</strong></p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">select e.ename,e.sal,d.dname from emp e left outer join dept d</span><br><span class="line">where e.sal &gt; all (select sal from emp where edid=30) and e.edid = d.did;</span><br></pre></td></tr></table></figure></div>

      
    </div>
    
      <footer class="article-footer">
        完
      </footer>
    
  </div>
  
    
<nav id="article-nav">
  <div class="article-nav-block">
    
      <a href="/2018/10/11/JDBC的简单使用——MySQL/" id="article-nav-newer" class="article-nav-link-wrap">
        <strong class="article-nav-caption"></strong>
        <div class="article-nav-title">
          
            JDBC的简单使用——MySQL
          
        </div>
      </a>
    
  </div>
  <div class="article-nav-block">
    
      <a href="/2018/10/11/MySQL数据库操作（四）——多表之间的关系/" id="article-nav-older" class="article-nav-link-wrap">
        <div class="article-nav-title">MySQL数据库操作（四）——多表之间的关系</div>
        <strong class="article-nav-caption"></strong>
      </a>
    
  </div>
</nav>

    
  
  
</article>
</section>
        <aside id="sidebar">
  
    <div class="widget-box">
  <div class="avatar-box avatar-item">
    <img class="avatar" src="/uploads/artistic_image/head.jpg" title="头像来自网络"></img>
    <h3 class="avatar-name">
      
        海晨忆
      
    </h3>
    <p class="avatar-slogan">
      先谋生，再谋爱。人间值得，未来可期。
    </p>
  </div>
</div>


  
    
  <div class="widget-box">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Android-Socket/">Android -Socket</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-加密算法/">Android -加密算法</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-常用功能/">Android -常用功能</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-框架/">Android -框架</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-源码解析/">Android -源码解析</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-自定义view/">Android -自定义view</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-设计模式/">Android -设计模式</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/JAVA-并发/">JAVA -并发</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Java-SpringBoot/">Java -SpringBoot</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Tomcat/">Tomcat</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/kotlin/">kotlin</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/数据库-JDBC/">数据库 -JDBC</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/数据库-MySQL/">数据库 -MySQL</a></li></ul>
    </div>
  </div>


  
    

  
    
  
    
  <div class="widget-box">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/07/">July 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/11/">November 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">October 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/09/">September 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">August 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/07/">July 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">June 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/10/">October 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/05/">May 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">March 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/02/">February 2018</a></li></ul>
    </div>
  </div>

  
    
  <div class="widget-box">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2021/07/27/JAVA—公平锁，非公平锁，悲观锁，乐观锁，死锁/">JAVA—公平锁，非公平锁，悲观锁，乐观锁，死锁</a>
          </li>
        
          <li>
            <a href="/2021/07/19/JAVA—线程同步器AQS/">JAVA—线程同步器AQS</a>
          </li>
        
          <li>
            <a href="/2021/07/07/Android模拟点击/">Android模拟点击</a>
          </li>
        
          <li>
            <a href="/2019/11/27/SpringBoot系列-消息-RabbitMQ-（二十）/">SpringBoot系列-消息(RabbitMQ)（二十）</a>
          </li>
        
          <li>
            <a href="/2019/11/13/SpringBoot系列-Docker（十九）/">SpringBoot系列-Docker（十九）</a>
          </li>
        
      </ul>
    </div>
  </div>

  
      <div class="widget-box">
    <h3 class="widget-title">友情链接</h3>
    <div class="widget">
      
        <a class="hrf" style="display: block;" href="https://github.com/haichenyi" title target='_blank'
        >Github</a>
      
        <a class="hrf" style="display: block;" href="https://www.jianshu.com/u/6077ee440c37" title target='_blank'
        >简书</a>
      
        <a class="hrf" style="display: block;" href="https://blog.csdn.net/qq_27634797" title target='_blank'
        >CSDN</a>
      
    </div>
  </div>

  
</aside>
      </div>
      <footer id="footer">
  <div class="foot-box footers global-width">
    &copy;2017-2021 海晨忆 &nbsp;&nbsp;
    <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span id="busuanzi_container_site_pv">阁下是第<span id="busuanzi_value_site_pv"></span>个访客</span>
  </div>
</footer>
      <script src="https://code.jquery.com/jquery-2.0.3.min.js"></script>
<script>
if (!window.jQuery) {
var script = document.createElement('script');
script.src = "/js/jquery-2.0.3.min.js";
document.body.write(script);
}
</script>

  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



    </div>
    <nav id="mobile-nav" class="mobile-nav-box">
  <div class="mobile-nav-img mobile-nav-top"></div>
  
    <a href="/" class="mobile-nav-link">首页</a>
  
    <a href="/archives" class="mobile-nav-link">归档</a>
  
    <a href="/about" class="mobile-nav-link">简历</a>
  
  <div class="mobile-nav-img  mobile-nav-bottom"></div>
</nav>    
  </div>
</body>
</html>